# Run regressions for foot traffic analysis


# Setup ----
options(scipen = 999)
library(caret)
library(tidyverse)
library(lfe)
library(stargazer)
library(xtable)



load("/02_Data/FL_visitor_1804_1910_tract.RData") # NOTE: Proprietary data, values of all variables have been replaced with * 
load("/02_Data/tract_flood_FL.RData")             # NOTE: Proprietary data, values of all variables have been replaced with * 
load("/02_Data/NFIP_Michael_Claim.RData")
load("/02_Data/tract2017.RData")
load("/02_Data/NFIPTakeup.RData")
load("/02_Data/FloodZone.RData")
load("/02_Data/SavingsAccount.RData")
load("/02_Data/SCI_v3.RData")




# tract-level data as of 2017 --------------------------------------------------
tract_data = tract2017 %>%
  left_join(tract_flood_FL, by=c("GEOID"="FIPS_tract")) %>%
  left_join(NFIPTakeup, by="GEOID") %>%
  left_join(FloodZone, by="GEOID") %>%
  left_join(SavingsAccount, by="GEOID") %>%
  left_join(SCI_v3, by=c("GEOID"="tract")) %>%
  mutate(FloodDepth_ft_tract_dummy = ifelse(FloodDepth_ft_tract>0, 1, 0))



# tract-business-time level data, April 2018 to October 2019 -------------------
FL_visitor_1804_1910 = FL_visitor_1804_1910_tract %>%
  left_join(NFIP_Michael_Claim, by=c("visitor_home_tract"="CensusTract", "month")) %>%
  left_join(tract_data, by=c("visitor_home_tract"="GEOID")) %>%
  mutate(
    FloodDepth_ft_tract_dummy = ifelse(FloodDepth_ft_tract>0, 1, 0),
    ClaimAmount               = ifelse(is.na(ClaimAmount), 0, ClaimAmount),
    WAvgPaidWeek              = ifelse(is.na(WAvgPaidWeek), 0, WAvgPaidWeek),
    month                     = factor(month)
  )

date.binarized = predict(dummyVars(formula = "~ month", FL_visitor_1804_1910, sep = '_'), FL_visitor_1804_1910)        # the "dummyVars" formula in the "caret" package can be used to binarize the categotical variable automatically
colnames(date.binarized) = substr(colnames(date.binarized)[1:length(colnames(date.binarized))], 7, nchar(colnames(date.binarized)[1:length(colnames(date.binarized))]))

FL_visitor_1804_1910 = cbind.data.frame(FL_visitor_1804_1910, date.binarized)
rm(date.binarized)








################################################################################
# Table 5: Flood Insurance Effect by Local Business Type
################################################################################

# Download & load local business categories by Delgado et al. (2016)
# SOURCE: URL: https://www.clustermapping.us/content/cluster-mapping-methodology, "US Cluster Definitions 2017 v2020-0323.csv"
# Download and load data as "ClustersList"

ClustersList <- read.csv("C:/Users/f408560/OneDrive - Freddie Mac/Submission/JRI/Replication Package/02_Data/ClustersList.csv", stringsAsFactors = F)
colnames(ClustersList)[1] = "NAICS"

LocalClustersList = ClustersList %>% filter(Traded_Local=="Local")


FL_visitor_1804_1910_tract_new1  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Motor Vehicle Products and Services",]$NAICS)
FL_visitor_1804_1910_tract_new2  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Food and Beverage Processing and Distribution",]$NAICS)
FL_visitor_1804_1910_tract_new3  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Retailing of Clothing and General Merchandise",]$NAICS)
FL_visitor_1804_1910_tract_new4  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Personal Services (Non-Medical)",]$NAICS)
FL_visitor_1804_1910_tract_new5  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Hospitality Establishments",]$NAICS)

FL_visitor_1804_1910_tract_new6  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Health Services",]$NAICS)
FL_visitor_1804_1910_tract_new7  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Financial Services",]$NAICS)
FL_visitor_1804_1910_tract_new8  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Entertainment and Media",]$NAICS)
FL_visitor_1804_1910_tract_new9  = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Real Estate, Construction, and Development",]$NAICS)
FL_visitor_1804_1910_tract_new10 = FL_visitor_1804_1910 %>% filter(naics_code %in% LocalClustersList[LocalClustersList$ClusterName=="Local Household Goods and Services",]$NAICS)


c1 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new1)

c2 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location  + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new2)

c3 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location  + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new3)

c4 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location  + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new4)

c5 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location  + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new5)

c6 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location  + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new6)


c7 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new7)

c8 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location  + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new8)

c9 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                               FloodDepth_ft_location  + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new9)

c10 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) +
                                                FloodDepth_ft_location  + dummy_brands +
                                                log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                                ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                                ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
             factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_tract_new10)



stargazer(c1, c2, c3, c4, c5,
          c6, c7, c8, c9, c10,

          type = 'text',
          #         type = 'latex',
          omit = c("NAICS", "location", "2017", "brands", "Share"),
          omit.stat = c('ser'),
          omit.table.layout = 'n',
          
          float = F,
          column.sep.width = "2pt",
          notes.append = F,
          font.size="small"
          
)




################################################################################
# Table 6: Effect of Flood Insurance Take-up 
################################################################################
FL_visitor_1804_1910_new2 = FL_visitor_1804_1910 %>% filter(`18-04`==1 |
                                                            `18-05`==1 |
                                                            `18-06`==1 |
                                                            `18-07`==1 |
                                                            `18-08`==1 |
                                                            `18-09`==1 |
                                                            `18-10`==1 )

FL_visitor_1804_1910_new3 = FL_visitor_1804_1910 %>% filter(`18-04`==1 |
                                                            `18-05`==1 |
                                                            `18-06`==1 |
                                                            `18-07`==1 |
                                                            `18-08`==1 |
                                                            `18-09`==1 |
                                                            `18-10`==1 |
                                                            `18-11`==1 |
                                                            `18-12`==1 |
                                                            `19-01`==1)

FL_visitor_1804_1910_new4 = FL_visitor_1804_1910 %>% filter(`18-04`==1 |
                                                            `18-05`==1 |
                                                            `18-06`==1 |
                                                            `18-07`==1 |
                                                            `18-08`==1 |
                                                            `18-09`==1 |
                                                            `18-10`==1 |
                                                            `18-11`==1 |
                                                            `18-12`==1 |
                                                            `19-01`==1 |
                                                            `19-02`==1 |
                                                            `19-03`==1 |
                                                            `19-04`==1)

d1 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) + 
                                               FloodDepth_ft_tract:(log(SCI_MichaelArea) + log(SCI_HighIncome) + log(SCI_LowIncome)) +
                                               FloodDepth_ft_location + NAICS_2digit_name + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_new2)

d2 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) + 
                                               FloodDepth_ft_tract:(log(SCI_MichaelArea) + log(SCI_HighIncome) + log(SCI_LowIncome)) +
                                               FloodDepth_ft_location + NAICS_2digit_name + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_new3)

d3 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) + 
                                               FloodDepth_ft_tract:(log(SCI_MichaelArea) + log(SCI_HighIncome) + log(SCI_LowIncome)) +
                                               FloodDepth_ft_location + NAICS_2digit_name + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910_new4)

d4 = felm(log(1+visitor_count_home_tract) ~ (FloodDepth_ft_tract + FloodDepth_ft_tract:log(1+ClaimAmount) + 
                                               FloodDepth_ft_tract:(log(SCI_MichaelArea) + log(SCI_HighIncome) + log(SCI_LowIncome)) +
                                               FloodDepth_ft_location + NAICS_2digit_name + dummy_brands +
                                               log_medincome2017 + log_population2017 + ptg_Unemployed2017 +
                                               ptg_white2017 + ptg_OwnerOccupied2017 + ptg_Mortgage2017 + ptg_Bachelor2017 + Gini2017 + 
                                               ptg_SavingsAccount2017 + FloodZone_Share_Dev_Area_tract):Post | 
            factor(visitor_home_tract_placekey) + factor(month) | 0 | visitor_home_tract, data = FL_visitor_1804_1910)



stargazer(d1, d2, d3, d4,
          type = 'text',
          #         type = 'latex',
          omit = c("NAICS", "location", "2017", "brands", "Share"),
          omit.stat = c('ser'),
          omit.table.layout = 'n',
          
          float = F,
          column.sep.width = "2pt",
          notes.append = F,
          font.size="small"
          
)
